package ipt.dao.f4;

import ipt.enumeracion.Rol;
import ipt.exp.AlumnoException;
import ipt.vo.Alumno;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AlumnoJDBCDAO implements AlumnoDAO {

	Connection connection = null;
	PreparedStatement ptmt = null;
	ResultSet resultSet = null;
 
	private final static String TABLE_NAME = "ALUMNOS";
	private final static String TABLE_NAME2 = "CURSO";
	// Columnas
	private final static String COL_ID = "id";
	private final static String COL_DNI = "dni";
	private final static String COL_NOMBRE = "nombre";
	private final static String COL_APELLIDO1 = "apellido1";
	private final static String COL_APELLIDO2 = "apellido2";
	private final static String COL_EDAD = "edad";
	private final static String COL_ROL = "rol";
	private final static String COL_EMAIL = "email";
	private final static String COL_ID_CURSO = "id_curso";

	public AlumnoJDBCDAO() {
		super();
	}

	/**
	 * Conecta a la Base de datos
	 * 
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		Connection conn;
		conn = ConnectionFactory.getInstance().getConnection();
		return conn;
	}

	/**
	 * Saca el listado completo de alumnos de la Base de Datos y la mete en un
	 * List
	 * 
	 * @return List<alumno> devuelve un listado de alumnos
	 */
	public List<Alumno> getAlumnos() {

		List<Alumno> listaAlu = new ArrayList<Alumno>();

		try {

			String queryString = "SELECT * FROM " + TABLE_NAME;

			connection = getConnection();

			ptmt = connection.prepareStatement(queryString);

			resultSet = ptmt.executeQuery();

			while (resultSet.next()) {

				Alumno alu = new Alumno();
				alu.setDni(resultSet.getString(COL_DNI));

				alu.setApellido1(resultSet.getString(COL_APELLIDO1));
				alu.setApellido2(resultSet.getString(COL_APELLIDO2));
				alu.setEdad(resultSet.getInt(COL_EDAD));
				alu.setId(resultSet.getInt(COL_ID));
				alu.setNombre(resultSet.getString(COL_NOMBRE));
				alu.setEmail(resultSet.getString(COL_EMAIL));

				int rol = resultSet.getInt(COL_ROL);

				switch (rol) {
				case 0:
					alu.setRol(Rol.TRABAJADOR);
					break;
				case 1:
					alu.setRol(Rol.JEFE);
					break;
				case 2:
					alu.setRol(Rol.CLIENTE);
					break;
				}

				listaAlu.add(alu);
				alu = null;

			}

			return listaAlu;

		} catch (AlumnoException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("\n--- SQLException caught ---\n");
			while (e != null) {
				System.out.println("Message: " + e.getMessage() + "\n");
				System.out.println("SQLState: " + e.getSQLState() + "\n");
				System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
				e = e.getNextException();
				System.out.println("");
			}
		} finally {

			try {
				if (resultSet != null)
					resultSet.close();
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				System.out.println("\n--- SQLException caught ---\n");
				while (e != null) {
					System.out.println("Message: " + e.getMessage() + "\n");
					System.out.println("SQLState: " + e.getSQLState() + "\n");
					System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
					e = e.getNextException();
					System.out.println("");
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return listaAlu;
	}

	/**
	 * busca en la Base de Datos los datos de un alumno por dni
	 * 
	 * @param dni
	 *            del alumno
	 * @return Alumno
	 */
	@Override
	public Alumno getAlumnoByDni(String dni) throws AlumnoException {
		try {
			List<Alumno> listaAlu = new ArrayList<Alumno>();
			String queryString = "SELECT * FROM " + TABLE_NAME + " WHERE "
					+ COL_DNI + "='" + dni + "'";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			resultSet = ptmt.executeQuery();
			Alumno alu = new Alumno();

			while (resultSet.next()) {
				alu.setDni(resultSet.getString(COL_DNI));
				alu.setApellido1(resultSet.getString(COL_APELLIDO1));
				alu.setApellido2(resultSet.getString(COL_APELLIDO2));
				alu.setEdad(resultSet.getInt(COL_EDAD));
				alu.setId(resultSet.getInt(COL_ID));
				alu.setNombre(resultSet.getString(COL_NOMBRE));
				alu.setEmail(resultSet.getString(COL_EMAIL));

				switch (resultSet.getInt(COL_ROL)) {
				case 0:
					alu.setRol(Rol.TRABAJADOR);
					break;
				case 1:
					alu.setRol(Rol.JEFE);
					break;
				case 2:
					alu.setRol(Rol.CLIENTE);
					break;
				}
				listaAlu.add(alu);
			}
			if (listaAlu.size() > 1) {

				throw (new AlumnoException(
						"ERROR: Existen mas de un usuario con el dni:" + dni));
			} else if (listaAlu.size() == 1) {

				return alu;
			} else {
				return null;
			}

		} catch (AlumnoException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("\n--- SQLException caught ---\n");
			while (e != null) {
				System.out.println("Message: " + e.getMessage() + "\n");
				System.out.println("SQLState: " + e.getSQLState() + "\n");
				System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
				e = e.getNextException();
				System.out.println("");
			}
		} finally {

			try {
				if (resultSet != null)
					resultSet.close();
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				System.out.println("\n--- SQLException caught ---\n");
				while (e != null) {
					System.out.println("Message: " + e.getMessage() + "\n");
					System.out.println("SQLState: " + e.getSQLState() + "\n");
					System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
					e = e.getNextException();
					System.out.println("");
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return null;
	}

	/**
	 * modifica en la Base de Datos los datos de un alumno
	 * 
	 * @param alu
	 *            un alumno
	 * @return boolean
	 */
	@Override
	public boolean udapteAlumno(Alumno alu) throws AlumnoException {

		try {
			String dni = alu.getDni();

			int rol = 0;
			switch (alu.getRol().toString().charAt(0)) {
			case 'T':
				rol = 0;
			case 'J':
				rol = 1;
			case 'C':
				rol = 2;
			}
			String queryString = "UPDATE " + TABLE_NAME + " SET " + COL_NOMBRE
					+ " ='" + alu.getNombre() + "'," + COL_APELLIDO1 + " ='"
					+ alu.getApellido1() + "'," + COL_APELLIDO2 + " ='"
					+ alu.getApellido2() + "'," + COL_EDAD + " ='"
					+ alu.getEdad() + "'," + COL_EMAIL + " ='" + alu.getEmail()
					+ "'," + COL_ROL + " ='" + rol + "'  WHERE DNI='" + dni
					+ "'";

			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			ptmt.executeUpdate();

			System.out.println("Datos modificados");
		} catch (SQLException e) {
			System.out.println("\n--- SQLException caught ---\n");
			while (e != null) {
				System.out.println("Message: " + e.getMessage() + "\n");
				System.out.println("SQLState: " + e.getSQLState() + "\n");
				System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
				e = e.getNextException();
				System.out.println("");
			}
		} finally {

			try {
				if (resultSet != null)
					resultSet.close();
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				System.out.println("\n--- SQLException caught ---\n");
				while (e != null) {
					System.out.println("Message: " + e.getMessage() + "\n");
					System.out.println("SQLState: " + e.getSQLState() + "\n");
					System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
					e = e.getNextException();
					System.out.println("");
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return false;
	}

	/**
	 * borra en la Base de Datos los datos de un alumno
	 * 
	 * @param dni
	 *            dni de un alumno
	 * @return boolean
	 */
	@Override
	public boolean deleteAlumno(String dni) throws AlumnoException {
		try {
			String queryString = "DELETE FROM " + TABLE_NAME + " WHERE "
					+ COL_DNI + "= '" + dni + "'";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			int i = ptmt.executeUpdate();
			if (i == 1) {
				return true;
			} else if (i > 1) {
				throw (new AlumnoException(
						"ERROR: Se han eliminado mas de una fila"));
			} else {
				return false;
			}

		} catch (SQLException e) {
			System.out.println("\n--- SQLException caught ---\n");
			while (e != null) {
				System.out.println("Message: " + e.getMessage() + "\n");
				System.out.println("SQLState: " + e.getSQLState() + "\n");
				System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
				e = e.getNextException();
				System.out.println("");
			}
		} finally {

			try {
				if (resultSet != null)
					resultSet.close();
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				System.out.println("\n--- SQLException caught ---\n");
				while (e != null) {
					System.out.println("Message: " + e.getMessage() + "\n");
					System.out.println("SQLState: " + e.getSQLState() + "\n");
					System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
					e = e.getNextException();
					System.out.println("");
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return false;
	}

	/**
	 * inserta en la Base de Datos los datos a un alumno
	 * 
	 * @param alu
	 *            un alumno
	 * @return boolean
	 */
	@Override
	public boolean insertAlumno(Alumno alu) throws AlumnoException {
		try {
			int rol = 0;
			switch (alu.getRol().toString().charAt(0)) {
			case 'T':
				rol = 0;
				break;
			case 'J':
				rol = 1;
				break;
			case 'C':
				rol = 2;
				break;
			}

			if (getAlumnoByDni(alu.getDni()) == null) {
				String queryString = "INSERT INTO " + TABLE_NAME + "("
						+ COL_DNI + "," + COL_NOMBRE + "," + COL_APELLIDO1
						+ "," + COL_APELLIDO2 + "," + COL_EDAD + "," + COL_ROL
						+ "," + COL_EMAIL + ")VALUES ('" + alu.getDni() + "','"
						+ alu.getNombre() + "','" + alu.getApellido1() + "','"
						+ alu.getApellido2() + "'," + alu.getEdad() + "," + rol
						+ ",'" + alu.getEmail() + "')";
				connection = getConnection();
				ptmt = connection.prepareStatement(queryString);
				ptmt.executeUpdate();
			} else {
				System.out
						.println("ERROR: ya existe el dni en la Base de Datos");
			}

		} catch (SQLException e) {
			System.out.println("\n--- SQLException caught ---\n");
			while (e != null) {
				System.out.println("Message: " + e.getMessage() + "\n");
				System.out.println("SQLState: " + e.getSQLState() + "\n");
				System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
				e = e.getNextException();
				System.out.println("");
			}
		} finally {

			try {
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				System.out.println("\n--- SQLException caught ---\n");
				while (e != null) {
					System.out.println("Message: " + e.getMessage() + "\n");
					System.out.println("SQLState: " + e.getSQLState() + "\n");
					System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
					e = e.getNextException();
					System.out.println("");
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return false;

	}

	/**
	 * busca en la Base de Datos todos los alumnos de un curso
	 * 
	 * @param id
	 *            de curso
	 * @return Alumno
	 */

	public List<Alumno> getAlumnoByCurso(String id) throws AlumnoException {
		try {
			List<Alumno> listaAlu = new ArrayList<Alumno>();
			String queryString = "SELECT * FROM " + TABLE_NAME + " WHERE "
					+ COL_ID_CURSO + "='" + id + "'";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			resultSet = ptmt.executeQuery();
			Alumno alu = new Alumno();

			while (resultSet.next()) {
				alu.setDni(resultSet.getString(COL_DNI));
				alu.setApellido1(resultSet.getString(COL_APELLIDO1));
				alu.setApellido2(resultSet.getString(COL_APELLIDO2));
				alu.setEdad(resultSet.getInt(COL_EDAD));
				alu.setId(resultSet.getInt(COL_ID));
				alu.setNombre(resultSet.getString(COL_NOMBRE));
				alu.setEmail(resultSet.getString(COL_EMAIL));

				switch (resultSet.getInt(COL_ROL)) {
				case 0:
					alu.setRol(Rol.TRABAJADOR);
					break;
				case 1:
					alu.setRol(Rol.JEFE);
					break;
				case 2:
					alu.setRol(Rol.CLIENTE);
					break;
				}
				listaAlu.add(alu);
			}

			return listaAlu;

		} catch (AlumnoException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("\n--- SQLException caught ---\n");
			while (e != null) {
				System.out.println("Message: " + e.getMessage() + "\n");
				System.out.println("SQLState: " + e.getSQLState() + "\n");
				System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
				e = e.getNextException();
				System.out.println("");
			}
		} finally {

			try {
				if (resultSet != null)
					resultSet.close();
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				System.out.println("\n--- SQLException caught ---\n");
				while (e != null) {
					System.out.println("Message: " + e.getMessage() + "\n");
					System.out.println("SQLState: " + e.getSQLState() + "\n");
					System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
					e = e.getNextException();
					System.out.println("");
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return null;
	}

	/**
	 * busca en la Base de Datos todos los alumnos de un curso
	 * 
	 * @param id
	 *            de curso
	 * @return Alumno
	 */

	public void matricularAlumno(Alumno alum) throws AlumnoException {
		try {
			String queryString = "SELECT * FROM " + TABLE_NAME2 + " WHERE "
					+ COL_ID + "=" + alum.getId_curso();
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			resultSet = ptmt.executeQuery();

			if (resultSet.next()) {
				ptmt.close();
				queryString = "SELECT * FROM " + TABLE_NAME + " WHERE "
						+ COL_DNI + "='" + alum.getDni() + "'";
				ptmt = connection.prepareStatement(queryString);
				
					resultSet = ptmt.executeQuery();
					if(!resultSet.next()){
						System.out.println("insert");
						insertAlumno(alum);
					} else{
						if (resultSet != null)
							resultSet.close();
					}
				
				queryString = "UPDATE " + TABLE_NAME + " SET " + COL_ID_CURSO
						+ " ='" + alum.getId_curso() + "' WHERE DNI='"
						+ alum.getDni() + "'";
				connection = getConnection();
				ptmt = connection.prepareStatement(queryString);
				System.out.println("update");
				ptmt.executeUpdate();

			} else {
				System.out
						.println("ERROR:No existe el curso para el que se quiere matricular al alumno");
			}

		} catch (SQLException e) {
			System.out.println("\n--- SQLException caught ---\n");
			while (e != null) {
				System.out.println("Message: " + e.getMessage() + "\n");
				System.out.println("SQLState: " + e.getSQLState() + "\n");
				System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
				e = e.getNextException();
				System.out.println("");
			}
		} finally {

			try {
			/*	if (resultSet != null)
					resultSet.close();*/
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				System.out.println("\n--- SQLException caught ---\n");
				while (e != null) {
					System.out.println("Message: " + e.getMessage() + "\n");
					System.out.println("SQLState: " + e.getSQLState() + "\n");
					System.out.println("ErrorCode: " + e.getErrorCode() + "\n");
					e = e.getNextException();
					System.out.println("");
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}

}
